package com.xrtn.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author zyn161616@163.com
 * @Description:    Excel工具类
 * @date 2017-11-30 13:37
 */
public class ExcelTool {
    //创建日志对象
    private static final Logger logger = LoggerFactory.getLogger(ExcelTool.class);
    public int totalRows; //sheet中总行数
    public static int totalCells; //每一行总单元格数
    public static final String EMPTY = "";
    public static final String POINT = ".";
    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
    public static SimpleDateFormat sdf =   new SimpleDateFormat("yyyy/MM/dd");



    /**
     * 读取Excel文件
     * @param file
     * @return
     */
    public Map<Integer,Object> readExcel(MultipartFile file)  {
        //如果文件不为空
        if(ObjUtils.isNotEmpty(file) || EMPTY.equals(file.getOriginalFilename().trim())){
            //获取文件后缀名
            String postfix = getPostfix(file.getOriginalFilename());
            //如果不为空
            if(!EMPTY.equals(postfix)){
                //如果文件是xls结尾的
                if(OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
                    //执行读取该格式文件方法
                    return readXls(file);
                }
                //如果文件是xlsx结尾
                if(OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){
                    //执行读取该格式文件方法
//                    return readXlsx(file);
                }
            }
        }
        //默认返回空
        return null;
    }


    /**
     * read the Excel 2003-2007 .xls
     * @param file
     * @return
     */
    public  Map<Integer, Object> readXls(MultipartFile file){
        Map<Integer, Object> resultMap = new HashMap<>();

        //IO流读取文件
        InputStream input = null;
        HSSFWorkbook wb = null;
        try {
            //获取文件输入流
            input = file.getInputStream();
            //创建文档
            wb = new HSSFWorkbook(input);
            //读取sheet页
            for(int i = 0 ; i < wb.getNumberOfSheets() ; i++){
                //获取当前页数据
                HSSFSheet sheet = wb.getSheetAt(i);
                //如果不为空
                if(ObjUtils.isNotEmpty(sheet)) {
                    //获取sheet中总行数
                    totalRows = sheet.getLastRowNum();
                    //获取sheet中总列数
                    totalCells = sheet.getRow(0).getLastCellNum();

                    //创建二位数组,用来存放每张sheet的数据
                    Object[][] sheetDatas = null;
                    //如果超过一行
                    if(totalRows > 1){
                        sheetDatas = new Object[totalRows][totalCells];
                        //循环取值，第一行是标题，那么从第二行开始
                        for (int r = 1; r < totalRows + 1; r++) {
                            //读取列，从第一列开始
                            for (int c = 0; c < totalCells; c++) {
                                HSSFCell cell = sheet.getRow(r).getCell(c);
                                if(cell != null && !cell.equals("")){
                                    Object obj = getCellValue(cell);
                                    //放入数组中
                                    sheetDatas[r - 1][c] = obj;
                                }else{
                                    //放入数组中
                                    sheetDatas[r - 1][c] = "";
                                }
                            }
                        }
                    }
                    resultMap.put(i, sheetDatas);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("Excel error"+e);
            return null;
        }finally {
            try {
                input.close();
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return resultMap;
    }

    /**
     * 根据当前Excel数据转格式
     * @param cell
     * @return
     */
    public static Object getCellValue(HSSFCell cell) {
        if(ObjUtils.isEmpty(cell)){
            return null;
        }
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                return cell.getRichStringCellValue().getString();
            case HSSFCell.CELL_TYPE_NUMERIC:
                return new DecimalFormat("#").format(cell.getNumericCellValue());
            case HSSFCell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();
            case HSSFCell.CELL_TYPE_FORMULA:
                return cell.getNumericCellValue();
        }
        return null;
    }


    /**
     * 读取 Excel 2010 .xlsx格式
     * @param file
     * @return
     */
    public List<Object[][]> readXlsx(MultipartFile file){
        //存放总数据
        List<Object[][]> workBooks = new ArrayList<>();
        //IO流读取文件
        InputStream input = null;
        XSSFWorkbook wb = null;
        try {
           //获取文件输入流
            input = file.getInputStream();
            // 创建文档
            wb = new XSSFWorkbook(input);
            //读取sheet页
            for(int i = 0 ; i < wb.getNumberOfSheets() ; i++){
                //获取当前页数据
                XSSFSheet sheet = wb.getSheetAt(i);
                //如果不为空
                if(ObjUtils.isNotEmpty(sheet)) {
                    //获取sheet中总行数
                    totalRows = sheet.getLastRowNum();
                    //获取sheet中总列数
                    totalCells = sheet.getRow(0).getLastCellNum();
                    //创建二位数组,用来存放每张sheet的数据
                    Object[][] sheetDatas = new Object[totalRows][totalCells];
                    //循环取值，第一行是标题，那么从第二行开始
                    for (int r = 1; r < totalRows+1; r++) {
                        //读取列，从第一列开始
                        for (int c = 0; c < totalCells; c++) {
                            //获取当前行的每一个cell
                            XSSFCell cell = sheet.getRow(r).getCell(c);
                            //放入数组中
                            sheetDatas[r-1][c] = cell;
                        }
                    }
                    workBooks.add(sheetDatas);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        //默认返回空
        return workBooks;
    }

    /**
     * 这是一个通用的方法
     * @param fileName 文件名
     * @param headers  表格属性列名数组
     * @param dataset  需要显示的数据集合
     * @param pattern  如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     */
    public static HSSFWorkbook exportExcel(HttpServletRequest request, HttpServletResponse response, String fileName, String[] headers, List<Object[]> dataset, String pattern) {
        String docsPath = request.getSession().getServletContext().getRealPath("docs");//docs文件夹目录
        String filePath = docsPath + File.separator + fileName+"-"+System.currentTimeMillis()+".xls";


        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet("Excel");
        // 生成一个表格标题行样式
        HSSFCellStyle style = getColumnTopStyle(workbook);
        // 生成非标题样式
        HSSFCellStyle style2 = getColumnStyle(workbook);

        HSSFRow row2=sheet.createRow(0);
        //创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("总预约量");
        row2.createCell(1).setCellValue("待就诊");
        row2.createCell(2).setCellValue("已到店");
        row2.createCell(3).setCellValue("已完成");
        row2.createCell(4).setCellValue("取消");
        //在sheet里创建第三行
        HSSFRow row3=sheet.createRow(1);
        row3.createCell(0).setCellValue(1);
        row3.createCell(1).setCellValue(2);
        row3.createCell(2).setCellValue(3);
        row3.createCell(3).setCellValue(4);
        row3.createCell(4).setCellValue(5);

        // 产生表格标题行
        HSSFRow row = sheet.createRow(2);
        //循环取值
        for (short i = 0; i < headers.length; i++) {
            //创建单元格
            HSSFCell cell = row.createCell(i);
            //设置样式
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            //设置单元格内容
            cell.setCellValue(text);
        }
        // 遍历集合数据，产生数据行
        Iterator<Object[]> it = dataset.iterator();
        int index = 2;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);//从第1行开始创建
            Object[] obj = (Object[]) it.next();
            for (short i = 0; i < obj.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                Object value = obj[i];
                String textValue = null;
                if (!"".equals(value) && value != null) {
                    if (value instanceof Integer) {
                        int intValue = (Integer) value;
                        cell.setCellValue(intValue);
                    } else if (value instanceof Float) {
                        float fValue = (Float) value;
                        cell.setCellValue(fValue);
                    } else if (value instanceof Double) {
                        double dValue = (Double) value;
                        cell.setCellValue(dValue);
                    } else if (value instanceof Long) {
                        long longValue = (Long) value;
                        cell.setCellValue(longValue);
                    } else if (value instanceof Date) {
                        Date date = (Date) value;
                        if(null==pattern||pattern.equals("")){
                            pattern="yyyy-MM-dd";
                        }
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        textValue = sdf.format(date);
                        cell.setCellValue(textValue);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        textValue = value.toString();
                        cell.setCellValue(textValue); // 设置单元格的值
                    }
                } else {
                    cell.setCellValue("");
                }
            }
        }

        //让列宽随着导出的列长自动适应
        for (int colNum = 0; colNum < headers.length; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                // 当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(colNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(colNum);
                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue() != null
                                ? currentCell.getStringCellValue().getBytes().length : 10;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            if (colNum == 0) {
                sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
            } else {
                sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
            }
        }
        try {
            if (createDir(docsPath)) {
                OutputStream out = new FileOutputStream(filePath);
                workbook.write(out);
                out.close();
                download(filePath, response);// 执行下载
                cleanFile(filePath);// 删除已完成下载的文件
            }
        }  catch (FileNotFoundException e) {
            e.printStackTrace();
        }catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    /**
     * 这是一个通用的方法
     * @param fileName 文件名
     * @param headers  表格属性列名数组
     * @param dataset  需要显示的数据集合
     * @param pattern  如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     */
    public static HSSFWorkbook commonExportExcel(HttpServletRequest request, HttpServletResponse response, String fileName, String[] headers, List<Object[]> dataset, String pattern) {
        String docsPath = request.getSession().getServletContext().getRealPath("docs");//docs文件夹目录
        String filePath = docsPath + File.separator + fileName+"-"+System.currentTimeMillis()+".xls";


        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet("Excel");
        // 生成一个表格标题行样式
        HSSFCellStyle style = getColumnTopStyle(workbook);
        // 生成非标题样式
        HSSFCellStyle style2 = getColumnStyle(workbook);
        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        //循环取值
        for (short i = 0; i < headers.length; i++) {
            //创建单元格
            HSSFCell cell = row.createCell(i);
            //设置样式
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            //设置单元格内容
            cell.setCellValue(text);
        }
        // 遍历集合数据，产生数据行
        Iterator<Object[]> it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);//从第1行开始创建
            Object[] obj = (Object[]) it.next();
            for (short i = 0; i < obj.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                Object value = obj[i];
                String textValue = null;
                if (!"".equals(value) && value != null) {
                    if (value instanceof Integer) {
                        int intValue = (Integer) value;
                        cell.setCellValue(intValue);
                    } else if (value instanceof Float) {
                        float fValue = (Float) value;
                        cell.setCellValue(fValue);
                    } else if (value instanceof Double) {
                        double dValue = (Double) value;
                        cell.setCellValue(dValue);
                    } else if (value instanceof Long) {
                        long longValue = (Long) value;
                        cell.setCellValue(longValue);
                    } else if (value instanceof Date) {
                        Date date = (Date) value;
                        if(null==pattern||pattern.equals("")){
                            pattern="yyyy-MM-dd";
                        }
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        textValue = sdf.format(date);
                        cell.setCellValue(textValue);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        textValue = value.toString();
                        cell.setCellValue(textValue); // 设置单元格的值
                    }
                } else {
                    cell.setCellValue("");
                }
            }
        }

        //让列宽随着导出的列长自动适应
        for (int colNum = 0; colNum < headers.length; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                // 当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(colNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(colNum);
                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue() != null
                                ? currentCell.getStringCellValue().getBytes().length : 10;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            if (colNum == 0) {
                sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
            } else {
                sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
            }
        }
        try {
            if (createDir(docsPath)) {
                OutputStream out = new FileOutputStream(filePath);
                workbook.write(out);
                out.close();
                download(filePath, response);// 执行下载
                cleanFile(filePath);// 删除已完成下载的文件
            }
        }  catch (FileNotFoundException e) {
            e.printStackTrace();
        }catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }
    /**
     * 列头单元格样式
     * @param workbook
     * @return
     */
    public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
        // 设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        // 设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体颜色
        font.setColor(HSSFColor.VIOLET.index);
        // 设置字体大小
        font.setFontHeightInPoints((short) 12);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 在样式用应用设置的字体;
        style.setFont(font);
        return style;
    }

    /**
     * 列数据信息单元格样式
     * @param workbook
     * @return
     */
    public static HSSFCellStyle getColumnStyle(HSSFWorkbook workbook) {
        // 设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        // 设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 10);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 设置字体名字
        font.setFontName("Courier New");
        // 在样式用应用设置的字体;
        style.setFont(font);

        return style;
    }

    /**
     * 创建目录
     * @param destDirName
     * @return  true 存在目录  false 不存在目录
     */
    public static boolean createDir(String destDirName) {
        File dir = new File(destDirName);
        if (dir.exists()) {
            return true;
        }
        if (!destDirName.endsWith(File.separator)) {
            destDirName = destDirName + File.separator;
        }
        // 创建目录
        if (dir.mkdirs()) {
            System.out.println("创建目录" + destDirName + "成功！");
            return true;
        } else {
            System.out.println("创建目录" + destDirName + "失败！");
            return false;
        }
    }

    /**
     * 导出下载,弹出下载框
     * @param path  文件下载路径
     * @param response
     */
    public static void download(String path, HttpServletResponse response) {
        try {
            // path是指欲下载的文件的路径。
            File file = new File(path);
            // 取得文件名。
            String filename = file.getName();
            // 以流的形式下载文件。
            InputStream fis = new BufferedInputStream(new FileInputStream(path));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            // 清空response
            response.reset();
            // 设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename, "UTF-8"));
            response.addHeader("Content-Length", "" + file.length());
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 清除文件
     * @param docsPath 文件路径
     */
    public static  void cleanFile(String docsPath) {
        File file = new File(docsPath);
        file.delete();
    }

    /**
     * 获得path的后缀名
     * @param path
     * @return
     */
    public static String getPostfix(String path){
        if(path==null || EMPTY.equals(path.trim())){
            return EMPTY;
        }
        if(path.contains(POINT)){
            return path.substring(path.lastIndexOf(POINT)+1,path.length());
        }
        return EMPTY;
    }
    /**
     * 单元格格式
     * @param xssfCell
     * @return
     */
    public static String getXValue(XSSFCell xssfCell){
        if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            String cellValue = "";
            if(XSSFDateUtil.isCellDateFormatted(xssfCell)){
                Date date = XSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue());
                cellValue = sdf.format(date);
            }else{
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(xssfCell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());
                if(strArr.equals("00")){
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

    /**
     * 单元格格式
     * @param hssfCell
     * @return
     */
    @SuppressWarnings({ "static-access", "deprecation" })
    public static String getHValue(HSSFCell hssfCell){
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            String cellValue = "";
            if(HSSFDateUtil.isCellDateFormatted(hssfCell)){
                Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());
                cellValue = sdf.format(date);
            }else{
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(hssfCell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());
                if(strArr.equals("00")){
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    public static void export(HttpServletResponse response)throws IOException{
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
//建立新的sheet对象（excel的表单）
        HSSFSheet sheet=wb.createSheet("成绩表");
//在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1=sheet.createRow(0);
//创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell=row1.createCell(0);
        //设置单元格内容
        cell.setCellValue("学员考试成绩一览表");
//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
//在sheet里创建第二行
        HSSFRow row2=sheet.createRow(1);
        //创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("姓名");
        row2.createCell(1).setCellValue("班级");
        row2.createCell(2).setCellValue("笔试成绩");
        row2.createCell(3).setCellValue("机试成绩");
        //在sheet里创建第三行
        HSSFRow row3=sheet.createRow(2);
        row3.createCell(0).setCellValue("李明");
        row3.createCell(1).setCellValue("As178");
        row3.createCell(2).setCellValue(87);
        row3.createCell(3).setCellValue(78);
        //.....省略部分代码
        //输出Excel文件
        OutputStream output=response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=details.xls");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
    }
}


/**
     * 自定义xssf日期工具类
     * @author lp
     *
     */
class XSSFDateUtil extends DateUtil {
        protected static int absoluteDay(Calendar cal, boolean use1904windowing) {
            return DateUtil.absoluteDay(cal, use1904windowing);
        }
    }

